<?php
    define('CONFIG_FILE',true);
    include '../config.php';

    define('DB_FILE',true);
    require_once '../Tool/DB/SqlDBManager.class.php';

    require '../Tool/Mail/PHPMailerAutoload.php';
    include_once '../Classes/PHPExcel.php';
    include_once './conn.php';

    session_start();

    $beforeDeviationSC100 = 0;
    $beforeDeviationSC95 = 0;
    $beforeDeviationSC90 = 0;
    $beforeDeviationSC85 = 0;
    $beforeDeviationSC80 = 0;
    $beforeDeviationSC75 = 0;
    $beforeDeviationSC70 = 0;
    $beforeDeviationSC65 = 0;
    $beforeDeviationSC60 = 0;
    $beforeDeviationSC55 = 0;
    $beforeDeviationSC50 = 0;
    $beforeDeviationSC45 = 0;
    $beforeDeviationSC40 = 0;
    $beforeDeviationSC35 = 0;
    $beforeDeviationSC30 = 0;
    $beforeDeviationSC25 = 0;
    $beforeDeviationSC20 = 0;
    $beforeDeviationSC15 = 0;
    $beforeDeviationSC10 = 0;
    $beforeDeviationSC5 = 0;
    $beforeDeviationSC0 = 0;

    $beforeDeviationPR100 = 0;
    $beforeDeviationPR95 = 0;
    $beforeDeviationPR90 = 0;
    $beforeDeviationPR85 = 0;
    $beforeDeviationPR80 = 0;
    $beforeDeviationPR75 = 0;
    $beforeDeviationPR70 = 0;
    $beforeDeviationPR65 = 0;
    $beforeDeviationPR60 = 0;
    $beforeDeviationPR55 = 0;
    $beforeDeviationPR50 = 0;
    $beforeDeviationPR45 = 0;
    $beforeDeviationPR40 = 0;
    $beforeDeviationPR35 = 0;
    $beforeDeviationPR30 = 0;
    $beforeDeviationPR25 = 0;
    $beforeDeviationPR20 = 0;
    $beforeDeviationPR15 = 0;
    $beforeDeviationPR10 = 0;
    $beforeDeviationPR5 = 0;
    $beforeDeviationPR0 = 0;

    $afterDeviationSC100 = 0;
    $afterDeviationSC95 = 0;
    $afterDeviationSC90 = 0;
    $afterDeviationSC85 = 0;
    $afterDeviationSC80 = 0;
    $afterDeviationSC75 = 0;
    $afterDeviationSC70 = 0;
    $afterDeviationSC65 = 0;
    $afterDeviationSC60 = 0;
    $afterDeviationSC55 = 0;
    $afterDeviationSC50 = 0;
    $afterDeviationSC45 = 0;
    $afterDeviationSC40 = 0;
    $afterDeviationSC35 = 0;
    $afterDeviationSC30 = 0;
    $afterDeviationSC25 = 0;
    $afterDeviationSC20 = 0;
    $afterDeviationSC15 = 0;
    $afterDeviationSC10 = 0;
    $afterDeviationSC5 = 0;
    $afterDeviationSC0 = 0;

    $afterDeviationPR100 = 0;
    $afterDeviationPR95 = 0;
    $afterDeviationPR90 = 0;
    $afterDeviationPR85 = 0;
    $afterDeviationPR80 = 0;
    $afterDeviationPR75 = 0;
    $afterDeviationPR70 = 0;
    $afterDeviationPR65 = 0;
    $afterDeviationPR60 = 0;
    $afterDeviationPR55 = 0;
    $afterDeviationPR50 = 0;
    $afterDeviationPR45 = 0;
    $afterDeviationPR40 = 0;
    $afterDeviationPR35 = 0;
    $afterDeviationPR30 = 0;
    $afterDeviationPR25 = 0;
    $afterDeviationPR20 = 0;
    $afterDeviationPR15 = 0;
    $afterDeviationPR10 = 0;
    $afterDeviationPR5 = 0;
    $afterDeviationPR0 = 0;

    $countBeforeDeviationSC = 0;
    $countAfterDeviationSC = 0;
    $countAfterDeviationPR = 0;
    $countBeforeDeviationPR = 0;

    $beforeDeviationTotalAverageSC = 0;
    $beforeDeviationTotalAveragePR = 0;
    $afterDeviationTotalAverageSC = 0;
    $afterDeviationTotalAveragePR = 0;

    $sqlDBManager = new SqlDBManager();

    $centreName=$_SESSION["centreName"];
    $sql = "Select CentreId from centre where CentreName=?";
    $parameters=array($centreName);

    $res=$sqlDBManager->queryRow($sql,$parameters);

    if(!empty($res)){
         $centreId = $res['CentreId'];
    }

    $today = date('Y-m-d');
    $sql = "Select * from Tracking where Date<? and CentreId=? and Status=?";  
    $parameters=array($today,$centreId,'Not Exported');

    $res=$sqlDBManager->queryRow($sql,$parameters);

    if(!empty($res)){
        $_SESSION["startDate"] = date('Y-m-01', strtotime($res['Date']));
        $_SESSION["endDate"] = date('Y-m-t', strtotime($res['Date']));
        
        $rowId = $res['TrackingId'];
        
        $startDate = $_SESSION["startDate"];
        $endDate = $_SESSION["endDate"];
        if ($centreId == 0) {
            $clwhere = "client.CentreId is not null";
            $where = "CentreId is not null";
        } else {
            $clwhere = "client.CentreId=$centreId";
            $where = "CentreId=$centreId";
        }

        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        $startDate1 = date('dmy', strtotime($startDate));
        $endDate1 = date('dmy', strtotime($endDate));
        $_SESSION["startDate1"] = $startDate1;
        $_SESSION["endDate1"] = $endDate1;
        //$objConditional1 = new PHPExcel_Style_Conditional();
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle('3')->getFont()->setBold(true);
        $objPHPExcel->getProperties()->setTitle("EldercareReport" . $startDate . "to" . $endDate);
        $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Eldercare Report from ' . $startDate1 . ' to ' . $endDate1 . ' for ' . $_SESSION["centreName"] . ' Centre');
        $objPHPExcel->getActiveSheet()->mergeCells('A1:N1');
        $objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $checkDateSNo = $startDate;
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(9);
        $objPHPExcel->getActiveSheet()->mergeCells('C3:D3');
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(11);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(11);
        $objPHPExcel->getActiveSheet()->getStyle('E3')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('E3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('G3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F3')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('G3')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->SetCellValue('A3', 'S/No');
        $objPHPExcel->getActiveSheet()->SetCellValue('B3', 'NRIC');
        $objPHPExcel->getActiveSheet()->SetCellValue('C3', 'Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('E3', 'Citizenship');
        $objPHPExcel->getActiveSheet()->SetCellValue('F3', 'Before Deviation');
        $objPHPExcel->getActiveSheet()->SetCellValue('G3', 'After Deviation');
        $rowCount = 3;
        $count = 1;
        $column = 'H';
        while ($checkDateSNo <= $endDate) {
            $sqlhols = "Select count(*) from holiday where Date='$checkDateSNo'";
            $resulthols = mysql_query($sqlhols);

            while ($row = mysql_fetch_array($resulthols)) {
                if ($row['count(*)'] == 0) {
                    $day = date('l', strtotime($checkDateSNo));
                    if ($day != "Sunday" && $day != "Saturday") {
                        $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $count);
                        $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
                        $count++;
                        $column++;
                    } else {
                        $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
                    }
                } else {
                    $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
                }
            }
        }
        $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, 'Total');
        $rowCount++;
        $column = 'A';
        $ttlattendance = 0;

        $sql = "SELECT * from Client where $where";
        $result = mysql_query($sql);
        $sno = 1;
        while ($row = mysql_fetch_array($result)) {

            $objPHPExcel->getActiveSheet()->mergeCells('C' . $rowCount . ':D' . $rowCount);
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $sno);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $row['NRIC']);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $row['CustomerName']);
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $row['Citizenship']);
            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $row['BeforeDeviation']);
            $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $row['AfterDeviation']);

            $checkDate = $startDate;
            $workingday = 0;
            $attendance = 0;
            $column = 'H';
            while ($checkDate <= $endDate) {
                $sqlhols1 = "Select count(*) from holiday where Date='$checkDate'";
                $resulthols1 = mysql_query($sqlhols1);

                while ($rowhol = mysql_fetch_array($resulthols1)) {
                    if ($rowhol['count(*)'] == 0) {
                        $day = date('l', strtotime($checkDate));
                        if ($day != "Sunday" && $day != "Saturday") {
                            $sqlattendance = "Select * from attendancerecord where date(SignInTimeStamp)='$checkDate' and NRIC='" . $row['NRIC'] . "' and $where";
                            $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                            $workingday++;
                            $resultattendance = mysql_query($sqlattendance);
                            if (mysql_num_rows($resultattendance) == 1) {
                                $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, '1');
                                $attendance++;
                            } else {
                                $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, '0');
                            }
                            $column++;
                        } else {
                            $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                        }
                    } else {
                        $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                    }
                }
            }
            $ttlattendance += $attendance;
            $avg = $ttlattendance / $sno;
            $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $attendance);

            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN
                    )
                )
            );
            $style2Array = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THICK, PHPExcel_Style_Border::BORDER_DOUBLE
                    )
                )
            );
            $objPHPExcel->getActiveSheet()->getStyle('A3:' . $column-- . $rowCount)->applyFromArray($styleArray);
            $sno++;
            $rowCount++;

            //Before Deviation
            if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 100) {
                $beforeDeviationSC100 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 95) {
                $beforeDeviationSC95 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 90) {
                $beforeDeviationSC90 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 85) {
                $beforeDeviationSC85 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 80) {
                $beforeDeviationSC80 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 75) {
                $beforeDeviationSC75 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 70) {
                $beforeDeviationSC70 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 65) {
                $beforeDeviationSC65 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 60) {
                $beforeDeviationSC60 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 55) {
                $beforeDeviationSC55 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 50) {
                $beforeDeviationSC50 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 45) {
                $beforeDeviationSC45 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 40) {
                $beforeDeviationSC40 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 35) {
                $beforeDeviationSC35 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 30) {
                $beforeDeviationSC30 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 25) {
                $beforeDeviationSC25 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 20) {
                $beforeDeviationSC20 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 15) {
                $beforeDeviationSC15 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 10) {
                $beforeDeviationSC10 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 5) {
                $beforeDeviationSC5 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 0) {
                $beforeDeviationSC0 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 100) {
                $beforeDeviationPR100 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 95) {
                $beforeDeviationPR95 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 90) {
                $beforeDeviationPR90 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 85) {
                $beforeDeviationPR85 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 80) {
                $beforeDeviationPR80 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 75) {
                $beforeDeviationPR75 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 70) {
                $beforeDeviationPR70 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 65) {
                $beforeDeviationPR65 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 60) {
                $beforeDeviationPR60 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 55) {
                $beforeDeviationPR55 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 50) {
                $beforeDeviationPR50 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 45) {
                $beforeDeviationPR45 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 40) {
                $beforeDeviationPR40 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 35) {
                $beforeDeviationPR35 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 30) {
                $beforeDeviationPR30 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 25) {
                $beforeDeviationPR25 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 20) {
                $beforeDeviationPR20 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 15) {
                $beforeDeviationPR15 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 10) {
                $beforeDeviationPR10 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 5) {
                $beforeDeviationPR5 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 0) {
                $beforeDeviationPR0 += $attendance;
            }

            //After Deviation
            if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 100) {
                $afterDeviationPR100 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 95) {
                $afterDeviationPR95 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 90) {
                $afterDeviationPR90 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 85) {
                $afterDeviationPR85 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 80) {
                $afterDeviationPR80 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 75) {
                $afterDeviationPR75 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 70) {
                $afterDeviationPR70 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 65) {
                $afterDeviationPR65 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 60) {
                $afterDeviationPR60 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 55) {
                $afterDeviationPR55 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 50) {
                $afterDeviationPR50 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 45) {
                $afterDeviationPR45 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 40) {
                $afterDeviationPR40 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 35) {
                $afterDeviationPR35 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 30) {
                $afterDeviationPR30 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 25) {
                $afterDeviationPR25 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 20) {
                $afterDeviationPR20 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 15) {
                $afterDeviationPR15 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 10) {
                $afterDeviationPR10 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 5) {
                $afterDeviationPR5 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 0) {
                $afterDeviationPR0 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 100) {
                $afterDeviationSC100 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 95) {
                $afterDeviationSC95 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 90) {
                $afterDeviationSC90 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 85) {
                $afterDeviationSC85 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 80) {
                $afterDeviationSC80 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 75) {
                $afterDeviationSC75 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 70) {
                $afterDeviationSC70 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 65) {
                $afterDeviationSC65 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 60) {
                $afterDeviationSC60 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 55) {
                $afterDeviationSC55 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 50) {
                $afterDeviationSC50 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 45) {
                $afterDeviationSC45 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 40) {
                $afterDeviationSC40 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 35) {
                $afterDeviationSC35 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 30) {
                $afterDeviationSC30 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 25) {
                $afterDeviationSC25 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 20) {
                $afterDeviationSC20 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 15) {
                $afterDeviationSC15 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 10) {
                $afterDeviationSC10 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 5) {
                $afterDeviationSC5 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 0) {
                $afterDeviationSC0 += $attendance;
            }
        }
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, 'Average Attendance:');
        $objPHPExcel->getActiveSheet()->mergeCells('A' . $rowCount . ':B' . $rowCount);
        $objPHPExcel->getActiveSheet()->getStyle('A' . $rowCount . ':C' . $rowCount)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $rowCount, $avg);
        $rowCount++;
        $rowCount++;
        $rowCount1 = $rowCount;
        $rowOrgCount1 = $rowCount;
        //Before Deviation first row
        $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':G' . $rowCount)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->mergeCells('B' . $rowCount . ':G' . $rowCount);
        $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':G' . $rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         //After Deviation first row
        $objPHPExcel->getActiveSheet()->getStyle('I' . $rowCount . ':N' . $rowCount)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->mergeCells('I' . $rowCount . ':N' . $rowCount);
        $objPHPExcel->getActiveSheet()->getStyle('I' . $rowCount . ':N' . $rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //setting title header
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $rowCount, 'Before Deviation');
        $objPHPExcel->getActiveSheet()->setCellValue('I' . $rowCount, 'After Deviation');
        $rowCount++;
         //setting 2nd rows for BD
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, 'Subsidy Level');
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, 'SC');
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, 'Average');
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, 'Subsidy Level');
        $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, 'PR');
        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, 'Average');
         //setting 2nd rows for AD
        $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, 'Subsidy Level');
        $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, 'SC');
        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowCount, 'Average');
        $objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowCount, 'Subsidy Level');
        $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowCount, 'PR');
        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowCount, 'Average');
        $rowCount++;
        $rowOrgCount = $rowCount;

         //BD table info
         //BEFORE DEVIATION DETAILS
        for ($j = 100; $j >= 0; $j-=5) {
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $j . '%');
            $sqlbdsc = "SELECT count(*) from client where BeforeDeviation=$j and Citizenship='SC' and $where";
            $sqlbdpr = "SELECT count(*) from client where BeforeDeviation=$j and Citizenship='PR' and $where";
            $resultbdsc = mysql_query($sqlbdsc);
            $resultbdpr = mysql_query($sqlbdpr);

            while ($rowbdsc = mysql_fetch_array($resultbdsc)) {
                if ($rowbdsc['count(*)'] == 0) {
                    $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, '0');
                    $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, '0');
                } else {
                    $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $rowbdsc['count(*)']);
                    $countBeforeDeviationSC +=$rowbdsc['count(*)'];

                    if ($j == 100) {
                        $average = $beforeDeviationSC100 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 95) {
                        $average = $beforeDeviationSC95 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 90) {
                        $average = $beforeDeviationSC90 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 85) {
                        $average = $beforeDeviationSC85 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 80) {
                        $average = $beforeDeviationSC80 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 75) {
                        $average = $beforeDeviationSC75 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 70) {
                        $average = $beforeDeviationSC70 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 65) {
                        $average = $beforeDeviationSC65 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 60) {
                        $average = $beforeDeviationSC60 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 55) {
                        $average = $beforeDeviationSC55 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 50) {
                        $average = $beforeDeviationSC50 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 45) {
                        $average = $beforeDeviationSC45 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 40) {
                        $average = $beforeDeviationSC40 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 35) {
                        $average = $beforeDeviationSC35 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 30) {
                        $average = $beforeDeviationSC30 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 25) {
                        $average = $beforeDeviationSC25 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 20) {
                        $average = $beforeDeviationSC20 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 15) {
                        $average = $beforeDeviationSC15 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 10) {
                        $average = $beforeDeviationSC10 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 5) {
                        $average = $beforeDeviationSC5 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($j == 0) {
                        $average = $beforeDeviationSC0 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    }
                }
            }

            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $j . '%');

            while ($rowbdpr = mysql_fetch_array($resultbdpr)) {
                if ($rowbdpr['count(*)'] == 0) {
                    $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, '0');
                    $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, '0');
                } else {
                    $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $rowbdpr['count(*)']);
                    $countBeforeDeviationPR +=$rowbdpr['count(*)'];

                    if ($j == 100) {
                        $average = $beforeDeviationPR100 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 95) {
                        $average = $beforeDeviationPR95 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 90) {
                        $average = $beforeDeviationPR90 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 85) {
                        $average = $beforeDeviationPR85 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 80) {
                        $average = $beforeDeviationPR80 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 75) {
                        $average = $beforeDeviationPR75 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 70) {
                        $average = $beforeDeviationPR70 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 65) {
                        $average = $beforeDeviationPR65 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 60) {
                        $average = $beforeDeviationPR60 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 55) {
                        $average = $beforeDeviationPR55 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 50) {
                        $average = $beforeDeviationPR50 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 45) {
                        $average = $beforeDeviationPR45 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 40) {
                        $average = $beforeDeviationPR40 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 35) {
                        $average = $beforeDeviationPR35 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 30) {
                        $average = $beforeDeviationPR30 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 25) {
                        $average = $beforeDeviationPR25 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 20) {
                        $average = $beforeDeviationPR20 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 15) {
                        $average = $beforeDeviationPR15 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 10) {
                        $average = $beforeDeviationPR10 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 5) {
                        $average = $beforeDeviationPR5 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($j == 0) {
                        $average = $beforeDeviationPR0 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    }
                }
            }
            $rowCount++;
        }

        $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':G' . $rowCount)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, 'Total');
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $countBeforeDeviationSC);
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $beforeDeviationTotalAverageSC);
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, 'Total');
        $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $countBeforeDeviationPR);
        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $beforeDeviationTotalAveragePR);

        //AFTER DEVIATION TABLE INFO
        for ($k = 100; $k >= 0; $k-=5) {
            $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowOrgCount, $k . '%');
            $sqladsc = "SELECT count(*) from client where AfterDeviation=$k and Citizenship='SC' and $where";
            $sqladpr = "SELECT count(*) from client where AfterDeviation=$k and Citizenship='PR' and $where";
            $resultadsc = mysql_query($sqladsc);
            $resultadpr = mysql_query($sqladpr);

            while ($rowadsc = mysql_fetch_array($resultadsc)) {
                if ($rowadsc['count(*)'] == 0) {
                    $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowOrgCount, '0');
                    $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, '0');
                } else {

                    $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowOrgCount, $rowadsc['count(*)']);
                    $countAfterDeviationSC +=$rowadsc['count(*)'];
                    if ($k == 100) {
                        $average = $afterDeviationSC100 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 95) {
                        $average = $afterDeviationSC95 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 90) {
                        $average = $afterDeviationSC90 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 85) {
                        $average = $afterDeviationSC85 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 80) {
                        $average = $afterDeviationSC80 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 75) {
                        $average = $afterDeviationSC75 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 70) {
                        $average = $afterDeviationSC70 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 65) {
                        $average = $afterDeviationSC65 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 60) {
                        $average = $afterDeviationSC60 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 55) {
                        $average = $afterDeviationSC55 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 50) {
                        $average = $afterDeviationSC50 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 45) {
                        $average = $afterDeviationSC45 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 40) {
                        $average = $afterDeviationSC40 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 35) {
                        $average = $afterDeviationSC35 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 30) {
                        $average = $afterDeviationSC30 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 25) {
                        $average = $afterDeviationSC25 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 20) {
                        $average = $afterDeviationSC20 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 15) {
                        $average = $afterDeviationSC15 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 10) {
                        $average = $afterDeviationSC10 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 5) {
                        $average = $afterDeviationSC5 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($k == 0) {
                        $average = $afterDeviationSC0 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    }
                }
            }

            $objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowOrgCount, $k . '%');

            while ($rowadpr = mysql_fetch_array($resultadpr)) {
                if ($rowadpr['count(*)'] == 0) {
                    $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowOrgCount, '0');
                    $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, '0');
                } else {
                    $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowOrgCount, $rowadpr['count(*)']);
                    $countAfterDeviationPR +=$rowadpr['count(*)'];

                    if ($k == 100) {
                        $average = $afterDeviationPR100 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 95) {
                        $average = $afterDeviationPR95 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 90) {
                        $average = $afterDeviationPR90 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 85) {
                        $average = $afterDeviationPR85 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 80) {
                        $average = $afterDeviationPR80 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 75) {
                        $average = $afterDeviationPR75 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 70) {
                        $average = $afterDeviationPR70 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 65) {
                        $average = $afterDeviationPR65 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 60) {
                        $average = $afterDeviationPR60 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 55) {
                        $average = $afterDeviationPR55 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 50) {
                        $average = $afterDeviationPR50 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 45) {
                        $average = $afterDeviationPR45 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 40) {
                        $average = $afterDeviationPR40 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 35) {
                        $average = $afterDeviationPR35 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 30) {
                        $average = $afterDeviationPR30 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 25) {
                        $average = $afterDeviationPR25 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 20) {
                        $average = $afterDeviationPR20 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 15) {
                        $average = $afterDeviationPR15 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 10) {
                        $average = $afterDeviationPR10 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 5) {
                        $average = $afterDeviationPR5 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($k == 0) {
                        $average = $afterDeviationPR0 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    }
                }
            }
            $rowOrgCount++;
        }
        $objPHPExcel->getActiveSheet()->getStyle('I' . $rowCount . ':N' . $rowCount)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, 'Total');
        $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, $countAfterDeviationSC);
        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowCount, $afterDeviationTotalAverageSC);
        $objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowCount, 'Total');
        $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowCount, $countAfterDeviationPR);
        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowCount, $afterDeviationTotalAveragePR);

        $rowCount = $rowCount - 1;
        $rowOrgCount = $rowOrgCount - 1;
        $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount1 . ':' . 'G' . $rowCount)->applyFromArray($styleArray);
        $objPHPExcel->getActiveSheet()->getStyle('I' . $rowOrgCount1 . ':' . 'N' . $rowOrgCount)->applyFromArray($styleArray);
        $rowCount++;
        $rowOrgCount++;
        $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':' . 'G' . $rowCount)->applyFromArray($style2Array);
        $objPHPExcel->getActiveSheet()->getStyle('I' . $rowOrgCount . ':' . 'N' . $rowOrgCount)->applyFromArray($style2Array);

        //$objPHPExcel->disconnectWorksheets();

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('../MonthlyReport/EldercareReport' . $startDate1 . 'to' . $endDate1 . '-' . $_SESSION["centreName"] . '.xlsx');
        include './email.php';

        if($flagSuccess==1){
            $query = "UPDATE Tracking SET Status='Exported' WHERE TrackingId=$rowId";
            $result = mysql_query($query);

            //creating the excel file
            $todayEndDate = date('Y-m-t', strtotime($today));
            $insertquery = "INSERT INTO tracking(Date,Status,CentreId) VALUES('$todayEndDate','Not Exported',$centreId)";
            $insertresult = mysql_query($insertquery);
        }
    }else{
        echo json_encode(array('msg'=>"Monthly Attendance Report has been sent already."));
    }
?>